package dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import model.Contract;
import model.Duty;
import model.Emp;
import util.DBUtil;
import util.Page;
import dao.ContractDAO;

public class ContractDAOImpl implements ContractDAO {

	private DBUtil dbu = new DBUtil();
	private Connection conn = null;
	private PreparedStatement pstat = null;
	private ResultSet rs = null;
	private boolean result = false;
	private String sql = null;
	SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

	public boolean addContract(Contract contract) {
		sql = "insert into contract(id,empid,contractno,Starttime,Endtime,dutiesid,Content,Supplement)"
				+ "values(contractseq.nextval,?,?,to_date(?,'yyyy-MM-dd'),to_date(?,'yyyy-MM-dd'),?,?,?)";
		try {
			conn = dbu.getConnection();
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, contract.getEmp().getId());
			pstat.setString(2, contract.getContractno());
			pstat.setString(3, sdf.format(contract.getStarttime()));
			pstat.setString(4, sdf.format(contract.getEndtime()));
			pstat.setInt(5, contract.getDuty().getId());
			pstat.setString(6, contract.getContent());
			pstat.setString(7, contract.getSupplement());
			pstat.executeUpdate();
			result = true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return result;
	}

	public boolean deleteByConno(int conno) {

		sql = "delete from contract where contractno =?";
		conn = dbu.getConnection();
		try {
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, conno);
			pstat.executeUpdate();
			result = true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return result;
	}

	public boolean deleteById(int id) {
		
		System.out.println("i'm deleting!!!!!!!");
		
		sql = "delete from contract where id=?";
		conn = dbu.getConnection();
		try {
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, id);
			if (pstat.executeUpdate() > 0) {
				result = true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return result;
	}

	public boolean deleteContract(Contract contract) {
		sql = "delete from contract where id=?";
		conn = dbu.getConnection();
		try {
			pstat = conn.prepareStatement(sql);
			pstat.setObject(1, contract);
			pstat.executeUpdate();
			result = true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return result;
	}

	public Page<Contract> getAllContract(int pageNo, int pageSize) {
		Page<Contract> page = new Page<Contract>(pageNo, pageSize);
		int totalCount = 0;
		List<Contract> list = new ArrayList<Contract>();
		try {
			conn = dbu.getConnection();
			String sql = "select count(*) from contract";
			pstat = conn.prepareStatement(sql);
			rs = pstat.executeQuery();
			if (rs.next()) {
				totalCount = rs.getInt(1);
			}
			page.setTotalCount(totalCount);
			sql = "select * from (select s.*,rownum rn from (select * from contract) s where rownum <=?) where rn >=?";
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, page.getPageNo() * pageSize);
			pstat.setInt(2, (page.getPageNo() - 1) * pageSize + 1);
			rs = pstat.executeQuery();
			while (rs.next()) {
				Contract contract = new Contract();
				contract.setId(rs.getInt(1));
				contract.setEmp(new EmpDAOImpl().getEmpById(rs.getInt(2)));
				contract.setContractno(rs.getString(3));
				contract.setStarttime(rs.getDate(4));
				contract.setEndtime(rs.getDate(5));
				contract.setDuty(new DutyDAOImpl().getDutyById(rs.getInt(6)));
				contract.setContent(rs.getString(7));
				contract.setSupplement(rs.getString(8));
				list.add(contract);
			}
			page.setList(list);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}

		return page;
	}

	public Contract getById(int id) {
		Contract contract = new Contract();
		try {
			conn = dbu.getConnection();
			String sql = "select name from teacher where id=?";
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, id);
			rs = pstat.executeQuery();
			while (rs.next()) {
				contract.setId(rs.getInt(1));
				contract.setEmp(new EmpDAOImpl().getEmpById(rs.getInt(2)));
				contract.setContractno(rs.getString(3));
				contract.setStarttime(rs.getDate(4));
				contract.setEndtime(rs.getDate(5));
				contract.setDuty(new DutyDAOImpl().getDutyById(rs.getInt(6)));
				contract.setContent(rs.getString(7));
				contract.setSupplement(rs.getString(8));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return contract;
	}

	public Contract getById(String conno) {
		Contract contract = new Contract();
		try {
			conn = dbu.getConnection();
			String sql = "select * from Contract where contractno=?";
			pstat = conn.prepareStatement(sql);
			pstat.setString(1, conno);
			rs = pstat.executeQuery();
			while (rs.next()) {
				contract.setId(rs.getInt(1));
				contract.setEmp(new EmpDAOImpl().getEmpById(rs.getInt(2)));
				contract.setContractno(rs.getString(3));
				contract.setStarttime(rs.getDate(4));
				contract.setEndtime(rs.getDate(5));
				contract.setDuty(new DutyDAOImpl().getDutyById(rs.getInt(6)));
				contract.setContent(rs.getString(7));
				contract.setSupplement(rs.getString(8));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return contract;
	}

	public Page<Contract> getContractByContent(int pageNo, int pageSize,
			String content) {
		Page<Contract> page = new Page<Contract>(pageNo, pageSize);
		int totalCount = 0;
		List<Contract> list = new ArrayList<Contract>();
		try {
			conn = dbu.getConnection();
			String sql = "select count(*) from contract where content like / ";
			pstat = conn.prepareStatement(sql);
			pstat.setString(1, content.toString());
			rs = pstat.executeQuery();
			if (rs.next()) {
				totalCount = rs.getInt(1);
			}
			page.setTotalCount(totalCount);
			sql = "select * from (select s.*,rownum rn from (select * from contract where content like / ) s where rownum <=?) where rn >=?";
			pstat = conn.prepareStatement(sql);
			pstat.setString(1, content.toString());
			pstat.setInt(2, page.getPageNo() * pageSize);
			pstat.setInt(3, (page.getPageNo() - 1) * pageSize + 1);
			rs = pstat.executeQuery();
			while (rs.next()) {
				Contract contract = new Contract();
				contract.setId(rs.getInt(1));
				contract.setEmp(new EmpDAOImpl().getEmpById(rs.getInt(2)));
				contract.setContractno(rs.getString(3));
				contract.setStarttime(rs.getDate(4));
				contract.setEndtime(rs.getDate(5));
				contract.setDuty(new DutyDAOImpl().getDutyById(rs.getInt(6)));
				contract.setContent(rs.getString(7));
				contract.setSupplement(rs.getString(8));
				list.add(contract);
			}
			page.setList(list);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return page;
	}

	public Page<Contract> getContractByTime(int pageNo, int pageSize,
			Date starttime, Date endtime) {
		Page<Contract> page = new Page<Contract>(pageNo, pageSize);
		int totalCount = 0;
		List<Contract> list = new ArrayList<Contract>();
		try {
			conn = dbu.getConnection();
			String sql = "select count(*) from contract between time>=? and time<=?";
			pstat = conn.prepareStatement(sql);
			pstat.setDate(1, (java.sql.Date) starttime);
			pstat.setDate(2, (java.sql.Date) endtime);
			rs = pstat.executeQuery();
			if (rs.next()) {
				totalCount = rs.getInt(1);
			}
			page.setTotalCount(totalCount);
			sql = "select * from (select s.*,rownum rn from (select * from contract where between time>=? and time<=?) s "
					+ "where rownum <=?) where rn >=?";
			pstat = conn.prepareStatement(sql);
			pstat.setDate(1, (java.sql.Date) starttime);
			pstat.setDate(2, (java.sql.Date) endtime);
			pstat.setInt(3, page.getPageNo() * pageSize);
			pstat.setInt(4, (page.getPageNo() - 1) * pageSize + 1);
			rs = pstat.executeQuery();
			while (rs.next()) {
				Emp emp = new Emp();
				Duty duty = new Duty();
				Contract contract = new Contract();
				EmpDAOImpl edi = new EmpDAOImpl();
				contract.setId(rs.getInt(1));
				emp = edi.getEmpById(rs.getInt(2));
				contract.setEmp(emp);
				contract.setContractno(rs.getString(3));
				contract.setStarttime(rs.getDate(4));
				contract.setEndtime(rs.getDate(5));
				duty.setId(rs.getInt(6));
				contract.setDuty(duty);
				contract.setContent(rs.getString(7));
				contract.setSupplement(rs.getString(8));
				list.add(contract);
			}
			page.setList(list);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return page;

	}

	public Page<Contract> getByType(int pageNo, int pageSize, Object object) {
		Page<Contract> page = new Page<Contract>(pageNo, pageSize);
		int totalCount = 0;
		List<Contract> list = new ArrayList<Contract>();
		try {
			if (object.getClass().getName() == "model.Contract") {
				conn = dbu.getConnection();
				String sql = "select count(*) from contract where id=?";
				pstat = conn.prepareStatement(sql);
				pstat.setInt(1, ((Contract) object).getId());
				rs = pstat.executeQuery();
				if (rs.next()) {
					totalCount = rs.getInt(1);
				}
				page.setTotalCount(totalCount);
				sql = "select * from (select d.*,rownum rn from (select * from contract where id=?) d where rownum <=?) where rn >=?";
				pstat = conn.prepareStatement(sql);
				pstat.setInt(1, ((Contract) object).getId());
				pstat.setInt(2, page.getPageNo() * pageSize);
				pstat.setInt(3, (page.getPageNo() - 1) * pageSize + 1);
			} else if (object.getClass().getName() == "model.Duty") {
				conn = dbu.getConnection();
				String sql = "select count(*) from contract where dutiesid=?";
				pstat = conn.prepareStatement(sql);
				pstat.setInt(1, ((Duty) object).getId());
				rs = pstat.executeQuery();
				if (rs.next()) {
					totalCount = rs.getInt(1);
				}
				page.setTotalCount(totalCount);
				sql = "select * from (select d.*,rownum rn from (select * from contract where dutiesid=?) d where rownum <=?) where rn >=?";
				pstat = conn.prepareStatement(sql);
				pstat.setInt(1, ((Duty) object).getId());
				pstat.setInt(2, page.getPageNo() * pageSize);
				pstat.setInt(3, (page.getPageNo() - 1) * pageSize + 1);
			} else if (object.getClass().getName() == "model.Emp") {
				conn = dbu.getConnection();
				String sql = "select count(*) from contract where empid=?";
				pstat = conn.prepareStatement(sql);
				pstat.setInt(1, ((Emp) object).getId());
				rs = pstat.executeQuery();
				if (rs.next()) {
					totalCount = rs.getInt(1);
				}
				page.setTotalCount(totalCount);
				sql = "select * from (select d.*,rownum rn from (select * from contract where empid=?) d where rownum <=?) where rn >=?";
				pstat = conn.prepareStatement(sql);
				pstat.setInt(1, ((Emp) object).getId());
				pstat.setInt(2, page.getPageNo() * pageSize);
				pstat.setInt(3, (page.getPageNo() - 1) * pageSize + 1);
			}
			rs = pstat.executeQuery();
			while (rs.next()) {
				Contract contract = new Contract();
				contract.setId(rs.getInt(1));
				contract.setEmp(new EmpDAOImpl().getEmpById(rs.getInt(2)));
				contract.setContractno(rs.getString(3));
				contract.setStarttime(rs.getDate(4));
				contract.setEndtime(rs.getDate(5));
				contract.setDuty(new DutyDAOImpl().getDutyById(rs.getInt(6)));
				contract.setContent(rs.getString(7));
				contract.setSupplement(rs.getString(8));
				list.add(contract);
			}
			page.setList(list);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return page;
	}

	public boolean updateContract(Contract contract) {
		try {
			conn = dbu.getConnection();
			String sql = "update contract set empid=?,contractno=?,starttime=?,endtime=?,dutiesid=?,content=?,supplement=? where id=?";
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, contract.getEmp().getId());
			pstat.setString(2, contract.getContractno());
			pstat.setDate(3, new java.sql.Date(contract.getStarttime()
					.getTime()));
			pstat.setDate(4, new java.sql.Date(contract.getEndtime().getTime()));
			pstat.setInt(5, contract.getDuty().getId());
			pstat.setString(6, contract.getContent());
			pstat.setString(7, contract.getSupplement());
			pstat.setInt(8, contract.getId());

			if (pstat.executeUpdate() > 0) {
				result = true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return result;
	}

}
